﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq.Dynamic;
using System.Configuration;
using IronOne.SalesOne.BusinessObjects;

namespace IronOne.SalesOne.DataObjects
{
    public class LinqMessages
    {
        public int pageSize = Convert.ToInt32(ConfigurationManager.AppSettings["GridCount"]);

        public List<Message> GetMessages(string sortExpression)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<MessageEntity> query = db.MessageEntities;

                if (sortExpression.Length > 0)
                {
                    string[] sort = sortExpression.Split(' ');
                    string sortColumn = sort[0];
                    string sortOrder = sort[1];

                    switch (sortColumn)
                    {
                        case "MessageId":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.MessageId);
                            else
                                query = query.OrderByDescending(c => c.MessageId);
                            break;
                        //case "GroupCode":
                        //    if (sortOrder == "ASC")
                        //        query = query.OrderBy(c => c.GrooupCode);
                        //    else
                        //        query = query.OrderByDescending(c => c.GrooupCode);
                        //    break;
                        //case "GroupName":
                        //    if (sortOrder == "ASC")
                        //        query = query.OrderBy(c => c.GroupName);
                        //    else
                        //        query = query.OrderByDescending(c => c.GroupName);
                        //    break;
                    }
                }
                return query.Select(c => Mapper.ToMessageObject(c)).ToList();
            }
        }

        public List<Message> GetMessagesSentBy(int UserTypeId)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<MessageEntity> query = db.MessageEntities;
                //return query.Select(c => Mapper.ToUserRoleObject(c => c.UserTypeId==UserTypeId)).ToList();
                query = from p
                        in db.MessageEntities
                        where p.MessageFromUserTypeID == UserTypeId
                        select p;
                return query.Select(c => Mapper.ToMessageObject(c)).ToList();

            }
        }

        public List<Message> GetBroadcastedMessages()
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<MessageEntity> query = db.MessageEntities;
                //return query.Select(c => Mapper.ToUserRoleObject(c => c.UserTypeId==UserTypeId)).ToList();
                query = from p
                        in db.MessageEntities
                        where p.UserTypeId == 1
                        select p;
                return query.Select(c => Mapper.ToMessageObject(c)).ToList();

            }
        }

        public List<Message> GetIndividualMessages()
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<MessageEntity> query = db.MessageEntities;
                //return query.Select(c => Mapper.ToUserRoleObject(c => c.UserTypeId==UserTypeId)).ToList();
                query = from p
                        in db.MessageEntities
                        where p.UserTypeId != 1
                        select p;
                return query.Select(c => Mapper.ToMessageObject(c)).ToList();

            }
        }

        public List<Message> GetMessagesToBranches(int branchId)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<MessageEntity> query = db.MessageEntities;
                //return query.Select(c => Mapper.ToUserRoleObject(c => c.UserTypeId==UserTypeId)).ToList();
                query = from p
                        in db.MessageEntities
                        where p.UserTypeId == 3 & p.NodeId == branchId
                        select p;
                return query.Select(c => Mapper.ToMessageObject(c)).ToList();

            }
        }

        public List<Message> GetMessagesToUnits(int unitId)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<MessageEntity> query = db.MessageEntities;
                //return query.Select(c => Mapper.ToUserRoleObject(c => c.UserTypeId==UserTypeId)).ToList();
                query = from p
                        in db.MessageEntities
                        where p.UserTypeId == 4 & p.NodeId == unitId
                        select p;
                return query.Select(c => Mapper.ToMessageObject(c)).ToList();

            }
        }

        public List<Message> GetMeessagesForSOCode(string sOCode)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<MessageEntity> query = db.MessageEntities;
                //return query.Select(c => Mapper.ToUserRoleObject(c => c.UserTypeId==UserTypeId)).ToList();
                query = from p
                        in db.MessageEntities
                        where p.SoCodeId == sOCode
                        select p;
                return query.Select(c => Mapper.ToMessageObject(c)).ToList();

            }
        }

        public List<Message> SearchMessages(SearchMessage msg)
        {
            StringBuilder where = new StringBuilder();

            bool and = false;
            where.Append("MessageFromUserTypeId=" + msg.UserType.ToString());
            and = true;

            if (msg.Broadcasted != false)
                where.Append(" AND UserTypeId=1 ");
            else
                where.Append(" AND UserTypeId!=1 ");

            if (msg.Branches != null)
            {
                if (msg.Branches.Count > 0)
                {
                    if (and)
                        where.Append(" AND ");
                    where.Append(" UserTypeId=3 AND (");

                    foreach (Branch branch in msg.Branches)
                    {
                        where.Append(" NodeId = " + branch.BranchId.ToString());
                        if (msg.Branches.Count > 1)
                            where.Append(" or ");
                    }
                    if (where.ToString().EndsWith(" or "))
                        where.Remove(where.Length - 4, 4);
                    where.Append(")");
                }

            }
            if (msg.Units != null)
            {
                if (msg.Units.Count > 0)
                {
                    if (and)
                        where.Append(" AND (");

                    foreach (SalesOneUnit unit in msg.Units)
                    {
                        where.Append(" NodeId = " + unit.UnitId.ToString());
                        if (msg.Units.Count > 1)
                            where.Append(" or ");
                    }
                    if (where.ToString().EndsWith(" or "))
                        where.Remove(where.Length - 4, 4);
                    where.Append(")");
                }


            }
            if (msg.SOCodes != null)
            {
                if (msg.SOCodes.Count > 0)
                {
                    if (and)
                        where.Append(" AND (");

                    foreach (string SoCode in msg.SOCodes)
                    {
                        where.Append(" SoCodeId= \"" + SoCode + "\"");
                        if (msg.SOCodes.Count > 1)
                            where.Append(" or ");
                    }
                    if (where.ToString().EndsWith(" or "))
                        where.Remove(where.Length - 4, 4);

                    where.Append(")");
                }


            }

            if (where.Length > 1)
            {
                using (SODataDataContext db = DataContextFactory.CreateContext())
                {
                    IQueryable<MessageEntity> query = db.MessageEntities;
                    query = db.MessageEntities.Where(where.ToString());

                    return query.Select(c => Mapper.ToMessageObject(c)).ToList();
                }
            }
            else
                return null;
        }

        public List<Message> SearchMessages(SearchMessage msg, int pageNo, string sortExpression)
        {
            StringBuilder where = new StringBuilder();

            bool and = false;
            bool valid = true;
            where.Append("(MessageFromUserTypeId=" + msg.UserType.ToString()+")");
            and = true;
            LinqUnit linqUnit = new LinqUnit();
            LinqUser linqUser = new LinqUser();

            if (msg.SOCodes != null)
            {
                if (msg.SOCodes.Count > 0)
                {
                    if (valid)
                    {
                        if (and)
                            where.Append(" AND ((");
                        if(msg.Units.Count==1)
                            where.Append(" UserTypeId=4) AND (");

                        foreach (string SoCode in msg.SOCodes)
                        {
                            where.Append(" SoCodeId= \"" + SoCode + "\"");
                            if (msg.SOCodes.Count > 1)
                                where.Append(" or ");
                        }
                        if (where.ToString().EndsWith(" or "))
                            where.Remove(where.Length - 4, 4);

                        where.Append("))");
                    }
                }


            }

            if (msg.Units != null)
            {
                if (msg.Units.Count > 0)
                {
                    if (valid)
                    {
                        List<string> lstSoCodes;
                        if (and)
                            where.Append(" AND ");

                        where.Append(" (UserTypeId=4 AND (");

                        foreach (SalesOneUnit unit in msg.Units)
                        {
                            where.Append(" NodeId = " + unit.UnitId.ToString());
                            if (msg.Units.Count > 1)
                                where.Append(" or ");
                        }
                        if (where.ToString().EndsWith(" or "))
                            where.Remove(where.Length - 4, 4);
                        where.Append("))");

                        //foreach (SalesOneUnit unit in msg.Units)
                        //{
                        //    lstSoCodes=linqUser.GetUsersInUnits(unit.UnitId);

                        //    foreach (string SoCode in msg.SOCodes)
                        //    {
                        //    }
                        //}
                        valid = false;
                    }
                }


            }

            if (msg.Branches != null)
            {
                if (msg.Branches.Count > 0)
                {
                    if (valid)
                    {
                        
                        List<SalesOneUnit> lstUnits;
                        if (and)
                            where.Append(" AND ");
                        where.Append(" (UserTypeId=3 AND (");
                       
                        foreach (Branch branch in msg.Branches)
                        {
                            where.Append(" NodeId = " + branch.BranchId.ToString());   

                            if (msg.Branches.Count > 1)
                                where.Append(" or ");
                        }
                        if (where.ToString().EndsWith(" or "))
                            where.Remove(where.Length - 4, 4);
                        where.Append(")");

                        foreach (Branch branch in msg.Branches)
                        {
                            lstUnits = linqUnit.GetUnitsInBranch(branch.BranchId);

                            
                            if (lstUnits.Count > 0)
                            {
                                where.Append("or (UserTypeId=4 AND (");
                                foreach (SalesOneUnit unit in lstUnits)
                                {
                                    where.Append(" NodeId = " +unit.UnitId.ToString());
                                    if(lstUnits.Count>1)
                                        where.Append(" or ");

                                }
                                if (where.ToString().EndsWith(" or "))
                                    where.Remove(where.Length - 4, 4);
                                where.Append("))");
                            }
                        }
                        where.Append(")");
                        valid = false;
                    }
                }

            }


            if (msg.Broadcasted != false)
                where.Append(" AND (UserTypeId=1) ");
            else
                where.Append(" AND (UserTypeId!=1) ");




            if (where.Length > 1)
            {
                using (SODataDataContext db = DataContextFactory.CreateContext())
                {
                    IQueryable<MessageEntity> query = db.MessageEntities;
                    query = db.MessageEntities.Where(where.ToString());

                    if (sortExpression.Length > 0)
                    {
                        string[] sort = sortExpression.Split(' ');
                        string sortColumn = sort[0];
                        string sortOrder = sort[1];

                        switch (sortColumn)
                        {
                            case "MessageId":
                                if (sortOrder == "ASC")
                                    query = query.OrderBy(c => c.MessageId);
                                else
                                    query = query.OrderByDescending(c => c.MessageId);
                                break;

                            case "Upadated":
                                if (sortOrder == "ASC")
                                    query = query.OrderBy(c => c.LastUpdated);
                                else
                                    query = query.OrderByDescending(c => c.LastUpdated);
                                break;
                        }
                    }

                    var pageData = query.Skip((pageNo - 1) * pageSize).Take(pageSize);

                    return pageData.Select(c => Mapper.ToMessageObject(c)).ToList();
                }
            }
            else
                return null;
        }

        public List<Message> SearchMessages(int UserTypeId, bool broadcasted, int branchId, int unitId, string SOCodeId)
        {
            StringBuilder sb = new StringBuilder();
            if (UserTypeId != 0)
                sb.Append("MessageFromUserTypeId=" + UserTypeId.ToString()); //sb.Append( "MessageId=5 AND nodeid=3");
            if (broadcasted != false)
                sb.Append(" AND UserTypeId=1");
            else
                sb.Append(" AND UserTypeId!=1");
            if (branchId != 0)
                sb.Append(" AND UserTypeId=3 AND NodeId=" + branchId.ToString());
            if (unitId != 0)
                sb.Append(" AND UserTypeId=4 AND NodeId=" + unitId.ToString());
            if (SOCodeId != "")
                sb.Append(" AND SoCodeId=@0");

            if (sb.Length < 1)
            {
                if (sb.ToString().StartsWith(" AND "))
                    sb.Remove(0, 5);

                //MessageEntity a = new MessageEntity();
                using (SODataDataContext db = DataContextFactory.CreateContext())
                {
                    IQueryable<MessageEntity> query = db.MessageEntities;
                    //query= db.MessageEntities.Where(sb.ToString(),SOCodeId);
                    query = db.MessageEntities.Where(sb.ToString());
                    return query.Select(c => Mapper.ToMessageObject(c)).ToList();
                }
            }
            else
                return null;
        }

        public Message GetMessage(int messageID)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                return Mapper.ToMessageObject(db.MessageEntities.SingleOrDefault(p => p.MessageId == messageID));
            }
        }

        public int InsertMessage(Message message)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    MessageEntity entity = Mapper.ToMessageEntity(message);
                    db.MessageEntities.InsertOnSubmit(entity);
                    db.SubmitChanges();

                    message.Message_ID = entity.MessageId;
                    return entity.MessageId;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to message record was made before your changes.");
                }
                catch (Exception)
                {
                    throw new Exception("Unable to insert a message." + message.Message_ID);
                }
            }
        }

        public int InsertMessage(ManageMessages message)
        {
            Message msg = new Message();
            msg.MessageFromUserTypeID = message.FromUserTypeId;
            msg.UserTypeId = message.UserType;
            msg.Created = DateTime.Now;
            msg.CreatedBy = message.UserId;
            msg.MessageBody = message.Body;

            if (!message.Broadcasted)
            {
                if (message.Branches != null)
                {
                    if (message.Branches.Count > 0)
                    {
                        if (message.Branches.Count > 1)
                        {
                            msg.UserTypeId = 2;
                            msg.NodeId = message.GroupId;
                        }
                        else
                        {
                            msg.UserTypeId = 3;
                            msg.NodeId = message.Branches[0].BranchId;
                        }
                    }
                }
                if (message.Units != null)
                {
                    if (message.Units.Count > 0)
                    {
                        if (message.Units.Count > 1)
                        {
                            msg.UserTypeId = 3;
                            msg.NodeId = message.BranchId;
                        }
                        else
                        {
                            msg.UserTypeId = 4;
                            msg.NodeId = message.Units[0].UnitId;
                        }
                    }
                }
                if (message.SOCodes != null)
                {
                    if (message.SOCodes.Count > 0)
                    {
                        if (message.SOCodes.Count > 1)
                        {
                            msg.UserTypeId = 4;
                            msg.NodeId = message.UnitId;
                        }
                        else
                        {
                            msg.SoCodeId = message.SOCodes[0].ToString();
                            //msg.UserTypeId = -1;
                            //msg.NodeId = -1;
                        }
                    }
                }
            }
            else
            {
                msg.UserTypeId = 1;
                msg.NodeId = -1;
            }
            return InsertMessage(msg);
        }

        public int UpdateMessage(ManageMessages message)
        {
            Message msg = new Message();
            msg.Message_ID = message.MessageId;
            msg.MessageFromUserTypeID = message.FromUserTypeId;
            msg.UserTypeId = message.UserType;
            msg.ModifiedBy = message.UserId;
            msg.MessageBody = message.Body;
            msg.Created =(DateTime)message.Created;
            msg.CreatedBy =(Guid) message.CreatedBy;

            if (!message.Broadcasted)
            {
                if (message.Branches != null)
                {
                    if (message.Branches.Count > 0)
                    {
                        if (message.Branches.Count > 1)
                        {
                            msg.UserTypeId = 2;
                            msg.NodeId = message.GroupId;
                        }
                        else
                        {
                            msg.UserTypeId = 3;
                            msg.NodeId = message.Branches[0].BranchId;
                        }
                    }
                }
                if (message.Units != null)
                {
                    if (message.Units.Count > 0)
                    {
                        if (message.Units.Count > 1)
                        {
                            msg.UserTypeId = 3;
                            msg.NodeId = message.BranchId;
                        }
                        else
                        {
                            msg.UserTypeId = 4;
                            msg.NodeId = message.Units[0].UnitId;
                        }
                    }
                }
                if (message.SOCodes != null)
                {
                    if (message.SOCodes.Count > 0)
                    {
                        if (message.SOCodes.Count > 1)
                        {
                            msg.UserTypeId = 4;
                            msg.NodeId = message.UnitId;
                        }
                        else
                        {
                            msg.SoCodeId = message.SOCodes[0].ToString();
                            //msg.NodeId = -1;
                            //msg.UserTypeId = -1;
                        }
                    }
                }
            }
            else
            {
                msg.UserTypeId = 1;
                msg.NodeId = -1;
            }
            return UpdateMessage(msg);
        }

        public int UpdateMessage(Message message)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    MessageEntity entity = Mapper.ToMessageEntity(message);
                    db.MessageEntities.Attach(entity, true);
                    db.SubmitChanges();
                    return 1;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to message record was made before your changes.");
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }

        public int DeleteMessage(Message message)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    MessageEntity entity = Mapper.ToMessageEntity(message);
                    db.MessageEntities.Attach(entity, false);
                    db.MessageEntities.DeleteOnSubmit(entity);
                    db.SubmitChanges();

                    return 1;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to message record was made before your changes.");
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }

    }
}
